﻿SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Roman Kern>
-- Create date: <14.09.2011>
-- Description:	<Create a CLR specific configuration.>
-- Update:		<load all assemblies from command line and execute it.>
-- =============================================
CREATE PROCEDURE [dbo].[Your Script Name here]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	--We need the executing Assembly here that will retrieve out CLR Methods
	DECLARE @assembly_path nvarchar(4000) = 'Your CLR Module location here.',--'C:\CLR\CmdOutput.exe',
			@sql_batch nvarchar(max),
			@name nvarchar(max),
			@db_name nvarchar(max) = DB_NAME(),
			@do_nothing bit
	
	--Make Database Trustworthy, need to execute CLR DLL.
	IF not exists(select is_trustworthy_on from sys.databases where name=@db_name and is_trustworthy_on=1)
	begin
		set @sql_batch = 'ALTER DATABASE ' + @db_name + ' SET TRUSTWORTHY ON'
		exec sp_executesql @sql_batch
	end
	
    -- Enable Advanced options
	if not exists(select * from sys.configurations where name = 'show advanced options' and (value = 1 OR value_in_use = 1))
    begin 
		exec sp_configure 'show advanced options', 1
		RECONFIGURE
	end
	-- Enable CLR usage
	if not exists(select * from sys.configurations where name='clr enabled' and (value = 1 or value_in_use = 1))
	begin
		exec sp_configure 'clr enabled', 1
		RECONFIGURE
    end
    -- enable xp_cmdshell, we need it to execute our shell.
    if not exists(select * from sys.configurations where name = 'xp_cmdshell' and (value = 1 OR value_in_use = 1))
    begin 
		exec sp_configure 'xp_cmdshell', 1
		RECONFIGURE
	end
    
	declare @assemblies table (assembly_name nvarchar(max))
	
	insert into @assemblies(assembly_name)
    select
		name
	from sys.assemblies
	where	is_user_defined=1
	and		is_visible=1

	-- DROP ALL Extended procedures or functions
	set @sql_batch = ''
	SELECT @sql_batch += a.SCRIPT
	FROM (
		SELECT 
			CASE 
				WHEN type = 'FS' OR type = 'FT'  THEN 'DROP FUNCTION [' + name + ']'
				WHEN type = 'PC' THEN 'DROP PROCEDURE [' + name + ']'
				WHEN type = 'AF' THEN 'DROP AGGREGATE [' + name + ']'
				WHEN type = 'TA' THEN 'DROP TRIGGER [' + name + ']'
				ELSE '' END + CHAR(13) + CHAR(10) AS SCRIPT
		FROM sys.all_objects where type in ('FS', 'PC', 'AF', 'FT', 'TA') AND is_ms_shipped = 0
		-- prepare drop all Types.
		UNION ALL
		SELECT 'DROP TYPE [' + name + ']'
		FROM sys.types WHERE is_assembly_type = 1 AND is_user_defined = 1
	) a
	
	
	if LEN(ISNULL(@sql_batch, '')) > 0
	begin exec sp_executesql @sql_batch end

	
    IF @@ERROR<>0
    begin
		print 'Could not proceed. Drop CLR functions/procedures failed.'
		return
    end


	while ((SELECT COUNT(*) FROM @assemblies) > 0)
	BEGIN
		declare cur_all_user_def_assemblies cursor for
		SELECT assembly_name FROM @assemblies
		
		open cur_all_user_def_assemblies
		
		while 1=1
		begin
			fetch next from cur_all_user_def_assemblies into @name
		
			if @@FETCH_STATUS <> 0 begin break end
			BEGIN TRY
				set @sql_batch = 'DROP ASSEMBLY [' + @name + ']'
				exec sp_executesql @sql_batch
				DELETE FROM @assemblies where assembly_name = @name
			END TRY
			BEGIN CATCH
				set @do_nothing = 1
			END CATCH
		END
		close cur_all_user_def_assemblies
		deallocate cur_all_user_def_assemblies
	END

    -- Exit when error accures.
    IF @@ERROR<>0
    begin
	 SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
		return
    end
    --####################### Create Extended Procedures from Assembly ########################--
    declare @t table (line nvarchar(max))
    set @sql_batch = ''
    insert into @t exec xp_cmdshell @assembly_path
    -- get all query lines
    select @sql_batch += CHAR(13) + CHAR(10) + ISNULL(line, CHAR(13) + CHAR(10)) from @t
    exec sp_executesql @sql_batch
	--print @sql_batch
END
